using System;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;

namespace Security.Import
{
    public class ExcelDataReader
    {
        public event BusyFinishEventHander BusyFinish;

        public event BusyStartedEventHander BusyStarted;

        public event ExcelReaderCompleteEventHander ExcelReaderComplete;

        public event ExcelReaderErrorEventHander ExcelReaderError;

        public event ExcelReaderProcessEventHander ExcelReaderProcess;

        public ExcelDataReader(string excelFile)
        {
            this.FileName = excelFile;
        }

        public void ExcelReadByAdo(string fileName)
        {
            this.ExcelReadByAdo(fileName, "Sheet1");
        }

        public void ExcelReadByAdo(string fileName, string sheetNames)
        {
            this.RaiseBusyStartedEventHander();
            OdbcConnection connection = new OdbcConnection("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" + fileName + ";");
            OdbcCommand selectCommand = new OdbcCommand("SELECT * FROM [" + sheetNames + "$]", connection);
            OdbcDataAdapter adapter = new OdbcDataAdapter(selectCommand);
            DataSet dataSet = new DataSet();
            try
            {
                connection.Open();
                adapter.Fill(dataSet, sheetNames);
                this.RaiseBusyFinishEventHander();
                DataTable dt = dataSet.Tables[0];
                DataColumn column = new DataColumn("Ch?n", typeof(bool));
                dt.Columns.Add(column);
                int index = 0;
                this.RaiseExcelReaderProcessEventHander(0);
                while (index < dt.Rows.Count)
                {
                    dt.Rows[index]["Ch?n"] = true;
                    if (dt.Rows[index][0] == null)
                    {
                        dt.Rows.RemoveAt(index);
                    }
                    else if (dt.Rows[index][0].ToString() == "")
                    {
                        dt.Rows.RemoveAt(index);
                    }
                    else
                    {
                        index++;
                    }
                    this.RaiseExcelReaderProcessEventHander((index / dt.Rows.Count) * 100);
                }
                dt.Columns.Add("Error", typeof(int));
                dt.Columns.Add("ErrorText", typeof(string));
                this.RaiseExcelReaderCompleteEventHander(dt);
            }
            catch (Exception exception)
            {
                this.RaiseExcelReaderErrorEventHander(exception.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        public DataTable GetExcelSheetNames(string fileName)
        {
            OleDbConnection connection = null;
            DataTable oleDbSchemaTable = null;
            DataTable table2;
            try
            {
                connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"");
                connection.Open();
                oleDbSchemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (oleDbSchemaTable == null)
                {
                    return null;
                }
                table2 = oleDbSchemaTable;
            }
            catch (Exception)
            {
                table2 = null;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
                if (oleDbSchemaTable != null)
                {
                    oleDbSchemaTable.Dispose();
                }
            }
            return table2;
        }

        private void RaiseBusyFinishEventHander()
        {
            if (this.BusyFinish != null)
            {
                this.BusyFinish(this);
            }
        }

        private void RaiseBusyStartedEventHander()
        {
            if (this.BusyStarted != null)
            {
                this.BusyStarted(this);
            }
        }

        private void RaiseExcelReaderCompleteEventHander(DataTable dt)
        {
            if (this.ExcelReaderComplete != null)
            {
                this.ExcelReaderComplete(this, dt);
            }
        }

        private void RaiseExcelReaderErrorEventHander(string message)
        {
            if (this.ExcelReaderError != null)
            {
                this.ExcelReaderError(this, message);
            }
        }

        private void RaiseExcelReaderProcessEventHander(int position)
        {
            if (this.ExcelReaderProcess != null)
            {
                this.ExcelReaderProcess(this, position);
            }
        }

        public string FileName { get; set; }

        public delegate void BusyFinishEventHander(object sender);

        public delegate void BusyStartedEventHander(object sender);

        public delegate void ExcelReaderCompleteEventHander(object sender, DataTable dt);

        public delegate void ExcelReaderErrorEventHander(object sender, string message);

        public delegate void ExcelReaderProcessEventHander(object sender, int position);
    }
}